package springboot.study.project.util.db;

import net.sf.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import springboot.study.project.util.common.Util;

import java.sql.*;
import java.util.*;

public class BaseDao {

    // 批量执行阈值
    private static final int BATCH_NUM = 1000;

    private static final Logger logger = LoggerFactory.getLogger(BaseDao.class); // 日志打印

    /**
     * @Author: liu-hao
     * @Description: 执行sql查询数据
     * @Date: 2020/11/4 11:31
     * @Param: [sql, params]
     * @return: java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     **/
    public static List<Map<String, Object>> executeSqlQueryByParamObject(final String sql, final List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = DBUtil.getConnection();
            logger.debug("sql=" + Util.putSqlParams(sql, params));
            statement = conn.prepareStatement(sql);
            setPrepareValue(statement, params);
            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            while (resultSet.next()) {
                int columnCount = metaData.getColumnCount();
                Map<String, Object> row = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String colName = metaData.getColumnName(i).toLowerCase(Locale.getDefault());
                    Object colValue = resultSet.getObject(i);
                    if (Util.getStrOfObj(colValue).startsWith("{") && Util.getStrOfObj(colValue).endsWith("}")) {
                        try {
                            JSONObject jo = JSONObject.fromObject(colValue);
                            row.put(colName, jo);
                        } catch (Exception e) {
                            row.put(colName, Util.getNullStr(colValue));
                        }
                    } else {
                        row.put(colName, Util.getNullStr(colValue));
                    }
                    // row.put(metaData.getColumnName(i + 1).toLowerCase(Locale.getDefault()), resultSet.getObject(i + 1));
                }
                list.add(row);
            }
            return list;
        } catch (SQLException e) {
            logger.error("执行sql出错：sql=" + Util.putSqlParams(sql, params));
            e.printStackTrace();
            throw e;
        } catch (RuntimeException ex) {
            throw ex;
        } finally {
            DBUtil.closeConnection(conn, statement, null);
        }
    }


    /**
     * 单个执行插入
     *
     * @param sql    待执行的预编译SQL脚本
     * @param values 待更新记录所有参数
     * @return
     */
    public static boolean executeSqlByPre(String sql, List<Object> values) {
        int result = 0;
        Connection conn = null;
        PreparedStatement statement = null;

        // 1.加载驱动程序
        try {
            // 2.获得数据库链接
            conn = DBUtil.getConnection();

            // 预编译
            statement = conn.prepareStatement(sql);
            for (int i = 0; i < values.size(); i++) {
                statement.setObject(i + 1, values.get(i));
            }
            // 执行sql
            result = statement.executeUpdate();

            // 关闭资源
            DBUtil.closeConnection(conn, statement, null);
        } catch (SQLException e) {
            e.printStackTrace();
            DBUtil.closeConnection(conn, statement, null);
            logger.error("执行sql出错：sql=" + Util.putSqlParams(sql, values));
        }

        return result > 0 ? true : false;
    }

    /**
     * 批量预编译执行sql（update、delete、insert、merge）
     *
     * @param sql        待执行的预编译SQL脚本
     * @param valuesList 待更新记录所有参数的集合
     * @return 批量操作数据库更改记录条数
     * @throws SQLException
     */
    public static boolean batchExecuteSqlByPre(String sql, List<List<Object>> valuesList) {
        try {
            return _batchExecuteSqlByPre(sql, valuesList).intValue() == 0;// 成功
        } catch (RuntimeException ex) {
            throw ex;
        } catch (SQLException e) {
            logger.error("执行批处理出错！");
            throw new RuntimeException("执行SQL出错，错误信息：" + e.getSQLState());
        }
    }

    private static Integer _batchExecuteSqlByPre(String sql, List<List<Object>> valuesList) throws SQLException {

        Connection conn = null;
        PreparedStatement statement = null;
        List<Object> values = null;

        // 计数器
        int count = 0;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            statement = conn.prepareStatement(sql);
            for (int i = 0; i < valuesList.size(); i++) {
                values = valuesList.get(i);
                // logger.info("待执行的SQL：" + Util.putSqlParams(sql, values));
                for (int j = 0; j < values.size(); j++) {
                    Object value = values.get(j);
                    statement.setObject(j + 1, value);
                }
                statement.addBatch();
                // 每batchDealNum条记录，执行一次批量保存
                if (++count % BATCH_NUM == 0) {
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }

            statement.executeBatch();
            statement.clearBatch();
            conn.commit();
            conn.setAutoCommit(true);
            return Integer.valueOf(0);
        } catch (SQLException se) {
            se.printStackTrace();
            resetCommit(conn, true);
            // 进行批量保存报错后的处理，拼接sql，待后续执行找出报错的那条sql
            // logger.error("执行sql出错：sql=" + Util.putSqlParams(sql, values));
            logger.error("执行sql出错：sql=" + sql);
            logger.error("回滚成功！");
            throw new SQLException(se);
        } finally {
            DBUtil.closeConnection(conn, statement, null);
        }
    }

    /**
     * 回滚和设置为自动提交
     */
    private static void resetCommit(Connection conn, boolean autoCom) throws SQLException {
        conn.rollback();
        conn.setAutoCommit(autoCom);
    }

    /**
     * @return void
     * @Author liyongjie
     * @Email 316659146@qq.com
     * @Date 2020/8/3 9:29
     * @Param [statement, params]
     * @Description:
     */
    private static void setPrepareValue(PreparedStatement statement, List<Object> params) throws SQLException {
        //首先对连接和参数进行排序
        if (statement != null && params != null && params.size() > 0) {
            //对参数的类型进行设置时对常用类型判断
            for (int i = 0; i < params.size(); i++) {
                int index = i + 1;
                Object param = params.get(i);
                if (param instanceof Integer) {
                    statement.setInt(index, Util.getIntOfObj(param));
                } else if (param instanceof Long) {
                    statement.setLong(index, Util.getLongOfObj(param));
                } else if (param instanceof Float) {
                    statement.setFloat(index, Util.getFloatOfObj(param));
                } else if (param instanceof Double) {
                    statement.setDouble(index, Util.getDoubleOfObj(param));
                } else {
                    String str = Util.getStrOfObj(param).replaceAll("'", "");
                    statement.setObject(index, str);
                }
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        String sql = "select * from DIA_BS_EX_PM_PROPLAN_FCB_HIS";
        List<Map<String, Object>> result = executeSqlQueryByParamObject(sql, new ArrayList<>());
        System.out.println(result.size());
//        System.out.println(JSON.toJSONString(result));
//        new DataVerifyServiceImpl().executeDataVerify();
    }
}
